/* 소프트웨어공학 HW2 1113226, 1110359, 1110821
   멀티미디어과학과 고현숙, 박윤진, 이유진 */

import java.awt.Color;
import java.awt.Dimension;
import java.awt.FlowLayout;
import java.awt.Font;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;

public class Member extends JFrame implements ActionListener{ 
	JLabel top, title, info_label, name_label, phone_label, mobile_label, email_label, at_label, com_label, cname_label, dept_label, pos_label, fax_label;
	JTextField txt_name, txt_phone, txt_mobile, txt_emailf, txt_emailb, txt_cname, txt_dept, txt_pos, txt_fax;
	JTable table;
	JScrollPane scroll;
	JButton bt_regist, bt_password, bt_search, bt_edit, bt_delete;
	JPanel left, right, t, l1, l2, l3, r1, r2, r3, r4, r5, r6, r7, r8, r9, r10, r11;

	Login log;
	String driver, url, userid, passwd;
	Connection con;
	PreparedStatement pstmt;
	ResultSet rs;
	String g ;
	String sql;

	MemberModel model = new MemberModel();
	int selRow; 
	int idx; 

	public Member(Login log) {
		super("주소록 관리 프로그램_LPG");		
		this.log=log;		
		this.con=log.con;

		title = new JLabel("ADDRESS BOOK");
		Font f1 = new Font("serif", Font.BOLD, 30);
		title.setFont(f1);
		title.setForeground(Color.WHITE);

		top = new JLabel("VIEW");
		top.setFont(f1);
		top.setForeground(Color.PINK);

		info_label = new JLabel("<< Personal Information >>");
		Font f2 = new Font("serif", Font.BOLD, 20);
		info_label.setFont(f2);
		name_label = new JLabel("                              이             름");
		phone_label = new JLabel("                              전 화  번 호");
		mobile_label = new JLabel("                              핸드폰번호");
		email_label = new JLabel ("                              이메일주소 ");
		at_label = new JLabel("@");
		com_label = new JLabel("<< Company Information >>");
		com_label.setFont(f2);
		cname_label = new JLabel("                              회 사 이 름");
		dept_label = new JLabel("                              부 서 이 름");
		pos_label = new JLabel("                              담 당 직 책");
		fax_label = new JLabel("                              팩 스 번 호");
      
		txt_name = new JTextField(16);
		txt_phone = new JTextField(16);
		txt_mobile = new JTextField(16);
		txt_emailf = new JTextField(6);
		txt_emailb = new JTextField(7);
		txt_cname = new JTextField(16);
		txt_dept = new JTextField(16);
		txt_pos = new JTextField(16);
		txt_fax = new JTextField(16);

		bt_regist = new JButton("추가");
		bt_search = new JButton("검색");
		bt_password = new JButton("비밀번호 번경");
		bt_edit = new JButton("수정");
		bt_delete = new JButton("삭제");

		table = new JTable(model);		
		scroll = new JScrollPane(table);

		getContentPane().setLayout(new GridLayout(1,2));

		left = new JPanel();
		right = new JPanel();
		l1 = new JPanel();
		l2 = new JPanel();
		l3 = new JPanel();
        t = new JPanel();
		r1 = new JPanel();
		r2 = new JPanel();
		r3 = new JPanel();
		r4 = new JPanel();
		r5 = new JPanel();
		r6 = new JPanel();
		r7 = new JPanel();
		r8 = new JPanel();
		r9 = new JPanel();
		r10 = new JPanel();
		r11 = new JPanel();

		add(left);
		add(right);

		left.add(l1);
		left.add(l2);
		left.add(l3);

		l1.setPreferredSize(new Dimension(500, 50));
		l2.setPreferredSize(new Dimension(500, 433));
		l3.setPreferredSize(new Dimension(500, 45));

		l1.setBackground(Color.PINK);
		l1.add(title);

		l2.add(scroll);
		table.setAutoCreateRowSorter(true);
		//table.getColumnModel().getColumn(0).setPreferredWidth(100);
		//table.getColumnModel().getColumn(1).setPreferredWidth(200);

		l3.setLayout(new GridLayout(1,3));
		l3.add(bt_regist);
		l3.add(bt_search);
		l3.add(bt_password);

		right.setLayout(new GridLayout(12, 1));
		t.setBackground(Color.WHITE);
		r1.setBackground(Color.WHITE);
		r2.setBackground(Color.WHITE);
		r3.setBackground(Color.WHITE);
		r4.setBackground(Color.WHITE);
		r5.setBackground(Color.WHITE);
		r6.setBackground(Color.WHITE);
		r7.setBackground(Color.WHITE);
		r8.setBackground(Color.WHITE);
		r9.setBackground(Color.WHITE);
		r10.setBackground(Color.WHITE);
		r11.setBackground(Color.WHITE);

    right.add(t);
    right.add(r1);
		right.add(r2);
		right.add(r3);
		right.add(r4);
		right.add(r5);
		right.add(r6);
		right.add(r7);
		right.add(r8);
		right.add(r9);
		right.add(r10);
		right.add(r11);

		t.add(top);
		r1.add(info_label);
		r2.setLayout(new FlowLayout(FlowLayout.LEFT));
		r2.add(name_label);
		r2.add(txt_name);
		r3.setLayout(new FlowLayout(FlowLayout.LEFT));
		r3.add(phone_label);
		r3.add(txt_phone);
		r4.setLayout(new FlowLayout(FlowLayout.LEFT));
		r4.add(mobile_label);
		r4.add(txt_mobile);
		r5.setLayout(new FlowLayout(FlowLayout.LEFT));
		r5.add(email_label);
		r5.add(txt_emailf);
		r5.add(at_label);
		r5.add(txt_emailb);
	  r6.add(com_label);
		r6.setFont(f2);
		r7.setLayout(new FlowLayout(FlowLayout.LEFT));
		r7.add(cname_label);
		r7.add(txt_cname);
		r8.setLayout(new FlowLayout(FlowLayout.LEFT));
		r8.add(dept_label);
		r8.add(txt_dept);
		r9.setLayout(new FlowLayout(FlowLayout.LEFT));
		r9.add(pos_label);
		r9.add(txt_pos);
		r10.setLayout(new FlowLayout(FlowLayout.LEFT));
		r10.add(fax_label);
		r10.add(txt_fax);

		r11.setLayout(new GridLayout(1,2));
		r11.add(bt_edit);
		r11.add(bt_delete);

		bt_regist.addActionListener(this);
		bt_delete.addActionListener(this);
        bt_edit.addActionListener(this);

		table.addMouseListener(new MouseAdapter() {
			public void mouseClicked(MouseEvent e) {
				selRow = table.getSelectedRow();

				String name = (String) 	table.getValueAt(selRow, 0);
				String mobile = (String) table.getValueAt(selRow, 1);
				String cname = (String) table.getValueAt(selRow, 2);
				String dept = (String) table.getValueAt(selRow, 3);
				String pos = (String) table.getValueAt(selRow, 4);
				idx = (Integer) model.getValueAt(selRow, 5);
				String phone = (String) model.getValueAt(selRow, 6);
				String email = (String) model.getValueAt(selRow, 7);				
				String fax = (String) model.getValueAt(selRow, 8);
				txt_name.setText(name);
				txt_mobile.setText(mobile);
				txt_cname.setText(cname);
				txt_dept.setText(dept);
				txt_pos.setText(pos);
				txt_phone.setText(phone);
				txt_emailf.setText(email.substring(0, email.indexOf("@")));
				txt_emailb.setText(email.substring(email.lastIndexOf("@")+1));
				txt_fax.setText(fax); 
			}
		});

		this.addWindowListener(new WindowAdapter() {
			public void windowClosing(WindowEvent e) {
				closeDB();
				System.exit(0);
			}
		});
      
		if(log.con!=null) {
			JOptionPane.showMessageDialog(getParent(), "접속 성공. 리스트 호출");
				if(getListAll()){
				}
				else{
					JOptionPane.showMessageDialog(getParent(), "리스트 호출 실패");				
				}
		}else{
			JOptionPane.showMessageDialog(getParent(), "접속실패");
		}


		this.setSize(950,575);
		this.setVisible(true);
		this.setResizable(false);	
	}


	public boolean getListAll() {
		boolean result = false;
		String sql = "select * from member order by idx desc";
		try {
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery(); 

		
			Vector list = new Vector();
			while (rs.next()) {
				
			    Vector record = new Vector();
				record.add(rs.getString("name"));
				record.add(rs.getString("mobile"));
				record.add(rs.getString("cname"));
				record.add(rs.getString("dept"));
				record.add(rs.getString("pos"));

				record.add(rs.getInt("idx"));

			
			    record.add(rs.getString("phone"));
				record.add(rs.getString("email"));
				record.add(rs.getString("fax"));
			
				list.add(record);
			}
			
			model.setList(list);
			this.repaint();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return result;
	}

	public int regist() {		
		int result = 0;
		sql = "Insert into member(name,phone,mobile,email,cname,dept,pos,fax)";
			sql = sql + "Value(?,?,?,?,?,?,?,?)";		
		try {			
 
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, txt_name.getText()); 
			pstmt.setString(2, txt_phone.getText());
			pstmt.setString(3, txt_mobile.getText());
			pstmt.setString(4, txt_emailf.getText()+"@"+txt_emailb.getText());
			pstmt.setString(5, txt_cname.getText());
			pstmt.setString(6, txt_dept.getText());
			pstmt.setString(7, txt_pos.getText());
			pstmt.setString(8, txt_fax.getText());
			result = pstmt.executeUpdate();
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		return result;
	}

	public int delete() {
		int result = 0;
		String sql = "Delete from member where idx=?";

		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, idx);
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return result;
	}

public int edit(){
		int result =0;
		String sql = "Update member set name=?, phone=?, mobile=?, email=?, cname=?, dept=?, pos=?, fax=? where idx=?";
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, txt_name.getText()); 
			pstmt.setString(2, txt_phone.getText());
			pstmt.setString(3, txt_mobile.getText());
			pstmt.setString(4, txt_emailf.getText()+"@"+txt_emailb.getText());
			pstmt.setString(5, txt_cname.getText());
			pstmt.setString(6, txt_dept.getText());
			pstmt.setString(7, txt_pos.getText());
			pstmt.setString(8, txt_fax.getText());
			pstmt.setInt(9, idx);
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public void clear(){		
		txt_name.setText("");
		txt_phone.setText(""); 
		txt_mobile.setText(""); 
		txt_emailf.setText(""); txt_emailb.setText("");
		txt_cname.setText("");
		txt_dept.setText("");
		txt_pos.setText("");
		txt_fax.setText("");
		txt_name.requestFocus();
	}

	public boolean check(){
		boolean result =false;
		if(txt_name.getText()==null || txt_name.getText

			().length()==0){
				JOptionPane.showMessageDialog(getParent(), "이름은 필수항목입니다.");
				txt_name.setText("");
				txt_name.requestFocus();

		}else if (txt_name.getText().length()>20){
			JOptionPane.showMessageDialog(getParent(), "이름은 20자 이내로 입력해주세요.");
			txt_name.setText("");
			txt_name.requestFocus();			

		}else if (txt_phone.getText().matches("[0-9]")) 
		{
			JOptionPane.showMessageDialog(getParent(), "전화번호를 정확히 입력해주세요.");
			txt_phone.setText("");
			txt_phone.requestFocus();

}else if (txt_mobile.getText().matches("[0-9]")) 
		{
			JOptionPane.showMessageDialog(getParent(), "핸드폰 번호를 정확히 입력해주세요.");
			txt_mobile.setText("");
			txt_mobile.requestFocus();
		}

		else{
			result = true;
		}
		return result;
	}


	public void closeDB() {
		if (rs != null)
			try {
				rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (pstmt != null)
			try {
				pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (con != null)
			try {
				con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}	


	public void actionPerformed(ActionEvent e) {
		Object obj = e.getSource();
		if (obj.equals(bt_regist)) {
			if(check()){
				int result = regist();
				if (result != 0) {
          JOptionPane.showMessageDialog(getParent(), "등록 성공");
					getListAll();
					clear();
					table.updateUI();
				} else {
					JOptionPane.showMessageDialog(getParent(), "등록 실패");
				}
			}
		}

	if (obj.equals(bt_edit)) {
			if(check()){
				if(JOptionPane.showConfirmDialog(getParent(),	(String)table.getValueAt(selRow, 0)+ "님의 정보를 수정 하시겠습니까?")==0){
					int result = edit();
					if(result !=0){
            JOptionPane.showMessageDialog(getParent(), "수정 성공");
						getListAll();
						clear();
						table.updateUI();
					}else{
						JOptionPane.showMessageDialog(getParent(), "수정 실패");
					}
				}
			}
		}

		if (obj.equals(bt_delete)) {
			if(JOptionPane.showConfirmDialog(getParent(),	

				(String) table.getValueAt(selRow, 0)+ "님의 정보를 삭제하시겠습니까?")==0){
					int result = delete();
					if (result != 0) {
						getListAll();
						clear();
						table.updateUI();		

					}
			}
		}
	}
}